using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using System.Data;
using Dapper;
using hao_Model;
using hao_Dto.User;
using hao_Common.Dapper.Repository;
using hao_Common.Dapper.Db;

namespace hao_Reppsitory.User
{
    /// <summary>
    /// 地址仓储实现
    /// </summary>
    public class AddressRepository : IAddressRepository
    {
        private readonly IDbConnectionFactory _connectionFactory;

        public AddressRepository(IDbConnectionFactory dbConnectionFactory)
        {
            _connectionFactory = dbConnectionFactory;
        }

        /// <summary>
        /// 根据ID获取地址
        /// </summary>
        public async Task<Address> GetByIdAsync(Guid id)
        {
            using var connection = _connectionFactory.CreateConnection();
            const string sql = @"
                SELECT * FROM user_addresses 
                WHERE Id = @Id AND Status = 1 
                LIMIT 1";
            
            return await connection.QueryFirstOrDefaultAsync<Address>(sql, new { Id = id });
        }

        /// <summary>
        /// 添加地址
        /// </summary>
        public async Task AddAsync(Address address)
        {
            using var connection = _connectionFactory.CreateConnection();
            const string sql = @"
                INSERT INTO user_addresses (
                    Id, UserId, ReceiverName, ReceiverPhone, Province, City, District, 
                    DetailAddress, PostalCode, IsDefault, AddressLabel, Longitude, 
                    Latitude, Status, CreateTime, UpdateTime
                ) VALUES (
                    @Id, @UserId, @ReceiverName, @ReceiverPhone, @Province, @City, @District, 
                    @DetailAddress, @PostalCode, @IsDefault, @AddressLabel, @Longitude, 
                    @Latitude, @Status, @CreateTime, @UpdateTime
                )";
            
            var parameters = new
            {
                Id = address.Id,
                UserId = address.UserId,
                ReceiverName = address.ReceiverName,
                ReceiverPhone = address.ReceiverPhone,
                Province = address.Province,
                City = address.City,
                District = address.District,
                DetailAddress = address.DetailAddress,
                PostalCode = address.PostalCode,
                IsDefault = address.IsDefault,
                AddressLabel = address.AddressLabel,
                Longitude = address.Longitude,
                Latitude = address.Latitude,
                Status = address.Status,
                CreateTime = address.CreateTime,
                UpdateTime = address.UpdateTime
            };
            
            // 添加调试信息
            Console.WriteLine($"调试：插入地址参数 - ReceiverName: {parameters.ReceiverName}, ReceiverPhone: {parameters.ReceiverPhone}");
            Console.WriteLine($"调试：插入地址参数 - DetailAddress: {parameters.DetailAddress}, AddressLabel: {parameters.AddressLabel}");
            
            await connection.ExecuteAsync(sql, parameters);
        }

        /// <summary>
        /// 更新地址
        /// </summary>
        public async Task UpdateAsync(Address address)
        {
            using var connection = _connectionFactory.CreateConnection();
            const string sql = @"
                UPDATE user_addresses SET 
                    ReceiverName = @ReceiverName,
                    ReceiverPhone = @ReceiverPhone,
                    Province = @Province,
                    City = @City,
                    District = @District,
                    DetailAddress = @DetailAddress,
                    PostalCode = @PostalCode,
                    IsDefault = @IsDefault,
                    AddressLabel = @AddressLabel,
                    Longitude = @Longitude,
                    Latitude = @Latitude,
                    UpdateTime = @UpdateTime
                WHERE Id = @Id AND Status = 1";
            
            await connection.ExecuteAsync(sql, address);
        }

        /// <summary>
        /// 删除地址（软删除）
        /// </summary>
        public async Task DeleteAsync(Guid id)
        {
            using var connection = _connectionFactory.CreateConnection();
            const string sql = @"
                UPDATE user_addresses 
                SET Status = 0, UpdateTime = @UpdateTime 
                WHERE Id = @Id";
            
            await connection.ExecuteAsync(sql, new { Id = id, UpdateTime = DateTime.UtcNow });
        }

        /// <summary>
        /// 根据用户ID获取地址列表
        /// </summary>
        public async Task<List<Address>> GetAddressesByUserIdAsync(Guid userId)
        {
            using var connection = _connectionFactory.CreateConnection();
            const string sql = @"
                SELECT * FROM user_addresses 
                WHERE UserId = @UserId AND Status = 1 
                ORDER BY IsDefault DESC, CreateTime DESC";
            
            var result = await connection.QueryAsync<Address>(sql, new { UserId = userId });
            return result.ToList();
        }

        /// <summary>
        /// 获取用户的默认地址
        /// </summary>
        public async Task<Address> GetDefaultAddressByUserIdAsync(Guid userId)
        {
            using var connection = _connectionFactory.CreateConnection();
            const string sql = @"
                SELECT * FROM user_addresses 
                WHERE UserId = @UserId AND IsDefault = 1 AND Status = 1 
                LIMIT 1";
            
            return await connection.QueryFirstOrDefaultAsync<Address>(sql, new { UserId = userId });
        }

        /// <summary>
        /// 设置默认地址（会将其他地址设为非默认）
        /// </summary>
        public async Task<bool> SetDefaultAddressAsync(Guid userId, Guid addressId)
        {
            using var connection = _connectionFactory.CreateConnection();
            using var transaction = connection.BeginTransaction();
            
            try
            {
                // 1. 先将该用户的所有地址设为非默认
                const string clearDefaultSql = @"
                    UPDATE user_addresses 
                    SET IsDefault = 0, UpdateTime = @UpdateTime 
                    WHERE UserId = @UserId AND Status = 1";
                
                await connection.ExecuteAsync(clearDefaultSql, 
                    new { UserId = userId, UpdateTime = DateTime.UtcNow }, transaction);

                // 2. 设置指定地址为默认
                const string setDefaultSql = @"
                    UPDATE user_addresses 
                    SET IsDefault = 1, UpdateTime = @UpdateTime 
                    WHERE Id = @AddressId AND UserId = @UserId AND Status = 1";
                
                var result = await connection.ExecuteAsync(setDefaultSql, 
                    new { AddressId = addressId, UserId = userId, UpdateTime = DateTime.UtcNow }, transaction);

                transaction.Commit();
                return result > 0;
            }
            catch
            {
                transaction.Rollback();
                throw;
            }
        }

        /// <summary>
        /// 分页搜索地址
        /// </summary>
        public async Task<(List<Address> addresses, int totalCount)> SearchAddressesAsync(AddressSearchDto searchDto)
        {
            using var connection = _connectionFactory.CreateConnection();
            
            var whereConditions = new List<string> { "Status = @Status" };
            var parameters = new DynamicParameters();
            parameters.Add("Status", searchDto.Status);

            // 构建查询条件
            if (searchDto.UserId.HasValue)
            {
                whereConditions.Add("UserId = @UserId");
                parameters.Add("UserId", searchDto.UserId.Value);
            }

            if (!string.IsNullOrWhiteSpace(searchDto.Keyword))
            {
                whereConditions.Add("(ReceiverName LIKE @Keyword OR ReceiverPhone LIKE @Keyword OR CONCAT(Province, City, District, DetailAddress) LIKE @Keyword)");
                parameters.Add("Keyword", $"%{searchDto.Keyword}%");
            }

            if (!string.IsNullOrWhiteSpace(searchDto.Province))
            {
                whereConditions.Add("Province = @Province");
                parameters.Add("Province", searchDto.Province);
            }

            if (!string.IsNullOrWhiteSpace(searchDto.City))
            {
                whereConditions.Add("City = @City");
                parameters.Add("City", searchDto.City);
            }

            if (!string.IsNullOrWhiteSpace(searchDto.District))
            {
                whereConditions.Add("District = @District");
                parameters.Add("District", searchDto.District);
            }

            if (searchDto.IsDefault.HasValue)
            {
                whereConditions.Add("IsDefault = @IsDefault");
                parameters.Add("IsDefault", searchDto.IsDefault.Value);
            }

            var whereClause = string.Join(" AND ", whereConditions);
            
            // 映射排序字段名到数据库列名
            var dbSortBy = MapSortFieldToDbColumn(searchDto.SortBy);
            var orderClause = $"ORDER BY {dbSortBy} {(searchDto.SortDesc ? "DESC" : "ASC")}";

            // 查询总数
            var countSql = $"SELECT COUNT(*) FROM user_addresses WHERE {whereClause}";
            var totalCount = await connection.QuerySingleAsync<int>(countSql, parameters);

            // 查询数据
            var offset = (searchDto.PageNumber - 1) * searchDto.PageSize;
            var dataSql = $@"
                SELECT * FROM user_addresses 
                WHERE {whereClause} 
                {orderClause} 
                LIMIT @PageSize OFFSET @Offset";
            
            parameters.Add("PageSize", searchDto.PageSize);
            parameters.Add("Offset", offset);

            var addresses = await connection.QueryAsync<Address>(dataSql, parameters);
            return (addresses.ToList(), totalCount);
        }

        /// <summary>
        /// 检查用户是否已有默认地址
        /// </summary>
        public async Task<bool> HasDefaultAddressAsync(Guid userId)
        {
            using var connection = _connectionFactory.CreateConnection();
            const string sql = @"
                SELECT COUNT(*) FROM user_addresses 
                WHERE UserId = @UserId AND IsDefault = 1 AND Status = 1";
            
            var count = await connection.QuerySingleAsync<int>(sql, new { UserId = userId });
            return count > 0;
        }

        /// <summary>
        /// 批量删除地址
        /// </summary>
        public async Task<bool> BatchDeleteAsync(List<Guid> addressIds)
        {
            if (addressIds == null || addressIds.Count == 0) return false;

            using var connection = _connectionFactory.CreateConnection();
            const string sql = @"
                UPDATE user_addresses 
                SET Status = 0, UpdateTime = @UpdateTime 
                WHERE Id = ANY(@AddressIds)";
            
            var result = await connection.ExecuteAsync(sql, 
                new { AddressIds = addressIds, UpdateTime = DateTime.UtcNow });
            
            return result > 0;
        }

        /// <summary>
        /// 获取地址区域统计
        /// </summary>
        public async Task<List<AddressRegionStatsDto>> GetRegionStatsAsync()
        {
            using var connection = _connectionFactory.CreateConnection();
            const string sql = @"
                SELECT 
                    Province AS Province,
                    City AS City,
                    District AS District,
                    COUNT(*) AS AddressCount,
                    COUNT(DISTINCT UserId) AS UserCount
                FROM user_addresses 
                WHERE Status = 1 
                GROUP BY Province, City, District 
                ORDER BY AddressCount DESC";
            
            var result = await connection.QueryAsync<AddressRegionStatsDto>(sql);
            return result.ToList();
        }

        /// <summary>
        /// 根据区域获取地址数量
        /// </summary>
        public async Task<int> GetAddressCountByRegionAsync(string province, string city, string district)
        {
            using var connection = _connectionFactory.CreateConnection();
            
            var whereConditions = new List<string> { "Status = 1" };
            var parameters = new DynamicParameters();

            if (!string.IsNullOrWhiteSpace(province))
            {
                whereConditions.Add("Province = @Province");
                parameters.Add("Province", province);
            }

            if (!string.IsNullOrWhiteSpace(city))
            {
                whereConditions.Add("City = @City");
                parameters.Add("City", city);
            }

            if (!string.IsNullOrWhiteSpace(district))
            {
                whereConditions.Add("District = @District");
                parameters.Add("District", district);
            }

            var whereClause = string.Join(" AND ", whereConditions);
            var sql = $"SELECT COUNT(*) FROM user_addresses WHERE {whereClause}";
            
            return await connection.QuerySingleAsync<int>(sql, parameters);
        }

        /// <summary>
        /// 软删除地址
        /// </summary>
        public async Task<bool> SoftDeleteAsync(Guid addressId)
        {
            using var connection = _connectionFactory.CreateConnection();
            const string sql = @"
                UPDATE user_addresses 
                SET Status = 0, UpdateTime = @UpdateTime 
                WHERE Id = @AddressId";
            
            var result = await connection.ExecuteAsync(sql, 
                new { AddressId = addressId, UpdateTime = DateTime.UtcNow });
            
            return result > 0;
        }

        /// <summary>
        /// 检查地址是否属于指定用户
        /// </summary>
        public async Task<bool> CheckAddressOwnershipAsync(Guid addressId, Guid userId)
        {
            using var connection = _connectionFactory.CreateConnection();
            const string sql = @"
                SELECT COUNT(*) FROM user_addresses 
                WHERE Id = @AddressId AND UserId = @UserId AND Status = 1";
            
            var count = await connection.QuerySingleAsync<int>(sql, 
                new { AddressId = addressId, UserId = userId });
            
            return count > 0;
        }

        /// <summary>
        /// 映射排序字段名到数据库列名
        /// </summary>
        private string MapSortFieldToDbColumn(string sortBy)
        {
            return sortBy switch
            {
                "CreateTime" => "CreateTime",
                "UpdateTime" => "UpdateTime", 
                "ReceiverName" => "ReceiverName",
                "ReceiverPhone" => "ReceiverPhone",
                "Province" => "Province",
                "City" => "City",
                "District" => "District",
                "DetailAddress" => "DetailAddress",
                "PostalCode" => "PostalCode",
                "IsDefault" => "IsDefault",
                "AddressLabel" => "AddressLabel",
                "UserId" => "UserId",
                "Status" => "Status",
                _ => "CreateTime" // 默认按创建时间排序
            };
        }
    }
}